﻿namespace Yipusoft.BaseDoc.DAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Runtime.InteropServices;
    using Yipusoft.BaseDoc.DBUtility;
    using Yipusoft.BaseDoc.Model;

    public class FileManager
    {
        public bool AddFile(DocumentFileInfo file)
        {
            using (SqlConnection connection = new SqlConnection(SqlHelper.CONN_STRING))
            {
                SqlParameter[] commandParameters = new SqlParameter[] { 
                    new SqlParameter("@ID", SqlDbType.UniqueIdentifier), new SqlParameter("@OldFileName", SqlDbType.NVarChar, 200), new SqlParameter("@NewFileName", SqlDbType.NVarChar, 200), new SqlParameter("@FilePath", SqlDbType.NVarChar, 500), new SqlParameter("@DepartmentID", SqlDbType.NVarChar, 50), new SqlParameter("@CreateUser", SqlDbType.NVarChar, 50), new SqlParameter("@CreateDate", SqlDbType.DateTime), new SqlParameter("@ParentID", SqlDbType.UniqueIdentifier), new SqlParameter("@HasChild", SqlDbType.Bit), new SqlParameter("@Level", SqlDbType.Int, 4), new SqlParameter("@FileSize", SqlDbType.Int, 4), new SqlParameter("@IsFolder", SqlDbType.Bit), new SqlParameter("@LastAccessDate", SqlDbType.DateTime), new SqlParameter("@Format", SqlDbType.NVarChar, 50), new SqlParameter("@StartDate", SqlDbType.DateTime), new SqlParameter("@EndDate", SqlDbType.DateTime), 
                    new SqlParameter("@OrderID", SqlDbType.Int, 4)
                 };
                commandParameters[0].Value = file.ID;
                commandParameters[1].Value = file.OldFileName;
                commandParameters[2].Value = file.NewFileName;
                commandParameters[3].Value = file.FilePath;
                commandParameters[4].Value = file.DepartmentID;
                commandParameters[5].Value = file.CreateUser;
                commandParameters[6].Value = file.CreateDate;
                commandParameters[7].Value = file.ParentID;
                commandParameters[8].Value = file.HasChild;
                commandParameters[9].Value = file.Level;
                commandParameters[10].Value = file.FileSize;
                commandParameters[11].Value = file.IsFolder;
                commandParameters[12].Value = file.LastAccessDate;
                commandParameters[13].Value = file.Format;
                commandParameters[14].Value = file.StartDate;
                commandParameters[15].Value = file.EndDate;
                commandParameters[0x10].Value = file.OrderID;
                return (SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, "File_Add", commandParameters) > 0);
            }
        }

        public bool DeleteFile(Guid ID)
        {
            using (SqlConnection connection = new SqlConnection(SqlHelper.CONN_STRING))
            {
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@ID", SqlDbType.UniqueIdentifier) };
                commandParameters[0].Value = ID;
                return (SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, "File_DeleteByID", commandParameters) > 0);
            }
        }

        public int GetChildFileCount(Guid ID, bool Flag)
        {
            using (SqlConnection connection = new SqlConnection(SqlHelper.CONN_STRING))
            {
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@ID", SqlDbType.UniqueIdentifier, 0x10), new SqlParameter("@Flag", SqlDbType.Bit) };
                commandParameters[0].Value = ID;
                commandParameters[1].Value = Flag;
                return (int) SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, "File_GetFileCount", commandParameters);
            }
        }

        public DocumentFileInfo GetFile(Guid ID)
        {
            using (SqlConnection connection = new SqlConnection(SqlHelper.CONN_STRING))
            {
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@ID", SqlDbType.UniqueIdentifier) };
                commandParameters[0].Value = ID;
                SqlDataReader reader = SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, "File_GetListByID", commandParameters);
                if (reader.Read())
                {
                    DocumentFileInfo info = new DocumentFileInfo();
                    info.ID = this.GetValue(reader["ID"]) ? ((Guid) reader["ID"]) : Guid.Empty;
                    info.OldFileName = this.GetValue(reader["OldFileName"]) ? reader["OldFileName"].ToString() : "";
                    info.NewFileName = this.GetValue(reader["NewFileName"]) ? reader["NewFileName"].ToString() : "";
                    info.FilePath = this.GetValue(reader["FilePath"]) ? reader["FilePath"].ToString() : "";
                    info.DepartmentID = this.GetValue(reader["DepartmentID"]) ? reader["DepartmentID"].ToString() : "";
                    info.CreateUser = this.GetValue(reader["CreateUser"]) ? reader["CreateUser"].ToString() : "";
                    info.CreateDate = new DateTime?(this.GetValue(reader["CreateDate"]) ? ((DateTime) reader["CreateDate"]) : DateTime.MinValue);
                    info.ParentID = this.GetValue(reader["ParentID"]) ? ((Guid) reader["ParentID"]) : Guid.Empty;
                    info.HasChild = this.GetValue(reader["HasChild"]) ? ((bool) reader["HasChild"]) : false;
                    info.Level = new int?(this.GetValue(reader["Level"]) ? ((int) reader["Level"]) : 0);
                    info.FileSize = new int?(this.GetValue(reader["FileSize"]) ? ((int) reader["FileSize"]) : 0);
                    info.IsFolder = this.GetValue(reader["IsFolder"]) ? ((bool) reader["IsFolder"]) : false;
                    info.LastAccessDate = new DateTime?(this.GetValue(reader["LastAccessDate"]) ? ((DateTime) reader["LastAccessDate"]) : DateTime.MinValue);
                    info.Format = this.GetValue(reader["Format"]) ? reader["Format"].ToString() : "";
                    info.StartDate = new DateTime?(this.GetValue(reader["StartDate"]) ? ((DateTime) reader["StartDate"]) : DateTime.MinValue);
                    info.EndDate = new DateTime?(this.GetValue(reader["EndDate"]) ? ((DateTime) reader["EndDate"]) : DateTime.MinValue);
                    info.OrderID = this.GetValue(reader["OrderID"]) ? ((int) reader["OrderID"]) : 0;
                    return info;
                }
                return null;
            }
        }

        public List<DocumentFileInfo> GetList(string where)
        {
            using (SqlConnection connection = new SqlConnection(SqlHelper.CONN_STRING))
            {
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@where", SqlDbType.VarChar, 0x1f40) };
                commandParameters[0].Value = where;
                SqlDataReader reader = SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, "File_GetListByWhere", commandParameters);
                List<DocumentFileInfo> list = new List<DocumentFileInfo>();
                while (reader.Read())
                {
                    DocumentFileInfo item = new DocumentFileInfo();
                    item.ID = this.GetValue(reader["ID"]) ? ((Guid) reader["ID"]) : Guid.Empty;
                    item.OldFileName = this.GetValue(reader["OldFileName"]) ? reader["OldFileName"].ToString() : "";
                    item.NewFileName = this.GetValue(reader["NewFileName"]) ? reader["NewFileName"].ToString() : "";
                    item.FilePath = this.GetValue(reader["FilePath"]) ? reader["FilePath"].ToString() : "";
                    item.DepartmentID = this.GetValue(reader["DepartmentID"]) ? reader["DepartmentID"].ToString() : "";
                    item.CreateUser = this.GetValue(reader["CreateUser"]) ? reader["CreateUser"].ToString() : "";
                    item.CreateDate = new DateTime?(this.GetValue(reader["CreateDate"]) ? ((DateTime) reader["CreateDate"]) : DateTime.MinValue);
                    item.ParentID = this.GetValue(reader["ParentID"]) ? ((Guid) reader["ParentID"]) : Guid.Empty;
                    item.HasChild = this.GetValue(reader["HasChild"]) ? ((bool) reader["HasChild"]) : false;
                    item.Level = new int?(this.GetValue(reader["Level"]) ? ((int) reader["Level"]) : 0);
                    item.FileSize = new int?(this.GetValue(reader["FileSize"]) ? ((int) reader["FileSize"]) : 0);
                    item.IsFolder = this.GetValue(reader["IsFolder"]) ? ((bool) reader["IsFolder"]) : false;
                    item.LastAccessDate = new DateTime?(this.GetValue(reader["LastAccessDate"]) ? ((DateTime) reader["LastAccessDate"]) : DateTime.MinValue);
                    item.Format = this.GetValue(reader["Format"]) ? reader["Format"].ToString() : "";
                    item.StartDate = new DateTime?(this.GetValue(reader["StartDate"]) ? ((DateTime) reader["StartDate"]) : DateTime.MinValue);
                    item.EndDate = new DateTime?(this.GetValue(reader["EndDate"]) ? ((DateTime) reader["EndDate"]) : DateTime.MinValue);
                    item.OrderID = this.GetValue(reader["OrderID"]) ? ((int) reader["OrderID"]) : 0;
                    list.Add(item);
                }
                return list;
            }
        }

        public List<DocumentFileInfo> GetList(string where, int pageIndex, int pageSize, out int Count)
        {
            using (SqlConnection connection = new SqlConnection(SqlHelper.CONN_STRING))
            {
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@where", SqlDbType.NVarChar), new SqlParameter("@pageindex", SqlDbType.Int, 4), new SqlParameter("@pagesize", SqlDbType.Int, 4), new SqlParameter("@Count", SqlDbType.Int, 4) };
                commandParameters[0].Value = where;
                commandParameters[1].Value = pageIndex;
                commandParameters[2].Value = pageSize;
                commandParameters[3].Direction = ParameterDirection.Output;
                SqlDataReader reader = SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, "File_GetListByWhere2", commandParameters);
                List<DocumentFileInfo> list = new List<DocumentFileInfo>();
                while (reader.Read())
                {
                    DocumentFileInfo item = new DocumentFileInfo();
                    item.ID = this.GetValue(reader["ID"]) ? ((Guid) reader["ID"]) : Guid.Empty;
                    item.OldFileName = this.GetValue(reader["OldFileName"]) ? reader["OldFileName"].ToString() : "";
                    item.NewFileName = this.GetValue(reader["NewFileName"]) ? reader["NewFileName"].ToString() : "";
                    item.FilePath = this.GetValue(reader["FilePath"]) ? reader["FilePath"].ToString() : "";
                    item.DepartmentID = this.GetValue(reader["DepartmentID"]) ? reader["DepartmentID"].ToString() : "";
                    item.CreateUser = this.GetValue(reader["CreateUser"]) ? reader["CreateUser"].ToString() : "";
                    item.CreateDate = new DateTime?(this.GetValue(reader["CreateDate"]) ? ((DateTime) reader["CreateDate"]) : DateTime.MinValue);
                    item.ParentID = this.GetValue(reader["ParentID"]) ? ((Guid) reader["ParentID"]) : Guid.Empty;
                    item.HasChild = this.GetValue(reader["HasChild"]) ? ((bool) reader["HasChild"]) : false;
                    item.Level = new int?(this.GetValue(reader["Level"]) ? ((int) reader["Level"]) : 0);
                    item.FileSize = new int?(this.GetValue(reader["FileSize"]) ? ((int) reader["FileSize"]) : 0);
                    item.IsFolder = this.GetValue(reader["IsFolder"]) ? ((bool) reader["IsFolder"]) : false;
                    item.LastAccessDate = new DateTime?(this.GetValue(reader["LastAccessDate"]) ? ((DateTime) reader["LastAccessDate"]) : DateTime.MinValue);
                    item.Format = this.GetValue(reader["Format"]) ? reader["Format"].ToString() : "";
                    item.StartDate = new DateTime?(this.GetValue(reader["StartDate"]) ? ((DateTime) reader["StartDate"]) : DateTime.MinValue);
                    item.EndDate = new DateTime?(this.GetValue(reader["EndDate"]) ? ((DateTime) reader["EndDate"]) : DateTime.MinValue);
                    item.OrderID = this.GetValue(reader["OrderID"]) ? ((int) reader["OrderID"]) : 0;
                    list.Add(item);
                }
                Count = (int) commandParameters[3].Value;
                return list;
            }
        }

        public DataTable GetMonthFileCount(string Where)
        {
            using (SqlConnection connection = new SqlConnection(SqlHelper.CONN_STRING))
            {
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@Where", SqlDbType.VarChar, 0x1f40) };
                commandParameters[0].Value = Where;
                DataSet dataSet = new DataSet();
                SqlHelper.FillDataset(connection, CommandType.StoredProcedure, "DocumentFile_GetMonthCount", dataSet, new string[] { "tb1" }, commandParameters);
                return dataSet.Tables[0];
            }
        }

        public int GetTempFileCount(string Departmentid, int year, int month, bool Flag)
        {
            using (SqlConnection connection = new SqlConnection(SqlHelper.CONN_STRING))
            {
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@Year", SqlDbType.Int, 4), new SqlParameter("@month", SqlDbType.Int, 4), new SqlParameter("@Flag", SqlDbType.Bit), new SqlParameter("@departmentid", SqlDbType.VarChar, 10) };
                commandParameters[0].Value = year;
                commandParameters[1].Value = month;
                commandParameters[2].Value = Flag;
                commandParameters[3].Value = Departmentid;
                return (int) SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, "File_GetTempFileCount", commandParameters);
            }
        }

        public int GetTempFileSize(string Departmentid, int year, int month)
        {
            using (SqlConnection connection = new SqlConnection(SqlHelper.CONN_STRING))
            {
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@departmentid", SqlDbType.VarChar, 10), new SqlParameter("@Year", SqlDbType.Int, 4), new SqlParameter("@month", SqlDbType.Int, 4) };
                commandParameters[0].Value = Departmentid;
                commandParameters[1].Value = year;
                commandParameters[2].Value = month;
                return ((SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, "File_GetTempFileSize", commandParameters) == DBNull.Value) ? 0 : ((int) SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, "File_GetTempFileSize", commandParameters)));
            }
        }

        private bool GetValue(object obj)
        {
            if (obj == null)
            {
                return false;
            }
            if (obj == DBNull.Value)
            {
                return false;
            }
            return true;
        }

        public bool UpdateDocumentFileInfo(DocumentFileInfo file)
        {
            using (SqlConnection connection = new SqlConnection(SqlHelper.CONN_STRING))
            {
                SqlParameter[] commandParameters = new SqlParameter[] { 
                    new SqlParameter("@ID", SqlDbType.UniqueIdentifier), new SqlParameter("@OldFileName", SqlDbType.NVarChar, 200), new SqlParameter("@NewFileName", SqlDbType.NVarChar, 200), new SqlParameter("@FilePath", SqlDbType.NVarChar, 500), new SqlParameter("@DepartmentID", SqlDbType.NVarChar, 50), new SqlParameter("@CreateUser", SqlDbType.NVarChar, 50), new SqlParameter("@CreateDate", SqlDbType.DateTime), new SqlParameter("@ParentID", SqlDbType.UniqueIdentifier), new SqlParameter("@HasChild", SqlDbType.Bit), new SqlParameter("@Level", SqlDbType.Int, 4), new SqlParameter("@FileSize", SqlDbType.Int, 4), new SqlParameter("@IsFolder", SqlDbType.Bit), new SqlParameter("@LastAccessDate", SqlDbType.DateTime), new SqlParameter("@Format", SqlDbType.NVarChar, 50), new SqlParameter("@StartDate", SqlDbType.DateTime), new SqlParameter("@EndDate", SqlDbType.DateTime), 
                    new SqlParameter("@OrderID", SqlDbType.Int, 4)
                 };
                commandParameters[0].Value = file.ID;
                commandParameters[1].Value = file.OldFileName;
                commandParameters[2].Value = file.NewFileName;
                commandParameters[3].Value = file.FilePath;
                commandParameters[4].Value = file.DepartmentID;
                commandParameters[5].Value = file.CreateUser;
                commandParameters[6].Value = file.CreateDate;
                commandParameters[7].Value = file.ParentID;
                commandParameters[8].Value = file.HasChild;
                commandParameters[9].Value = file.Level;
                commandParameters[10].Value = file.FileSize;
                commandParameters[11].Value = file.IsFolder;
                commandParameters[12].Value = file.LastAccessDate;
                commandParameters[13].Value = file.Format;
                commandParameters[14].Value = file.StartDate;
                commandParameters[15].Value = file.EndDate;
                commandParameters[0x10].Value = file.OrderID;
                return (SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, "File_Update", commandParameters) > 0);
            }
        }

        public bool UpdateFileSize(Guid ID)
        {
            using (SqlConnection connection = new SqlConnection(SqlHelper.CONN_STRING))
            {
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@ParentID", SqlDbType.UniqueIdentifier, 0x10) };
                commandParameters[0].Value = ID;
                return (SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, "File_UpdateFileSize", commandParameters) > 0);
            }
        }
    }
}

